﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using Travel_Model;
using System.Data;

namespace Travel_DAL
{
   public class Train_DAL
    {
        /// <summary>
        /// 连接数据库
        /// </summary>
        Database db = DatabaseFactory.CreateDatabase("Connection");
        /// <summary>
        /// 显示车次表信息
        /// </summary>
        /// <returns></returns>
        public List<trainnumber> Trainnumbershow()
        {
            string str = "select * from trainnumber";
            DbCommand cmd = db.GetSqlStringCommand(str);
            List<trainnumber> list = new List<trainnumber>();
            IDataReader da = db.ExecuteReader(cmd);
            while (da.Read())
            {
                trainnumber trainnumber = new trainnumber();
                trainnumber.Trainnumberid = Convert.ToInt32(da["Trainnumberid"]);
                trainnumber.Trainname = da["Trainname"].ToString();
                trainnumber.Starttime = da["Starttime"].ToString();
                trainnumber.Endtime = da["Endtime"].ToString();
                trainnumber.Stop = da["Stop"].ToString();
                trainnumber.Stoptime =da["Stoptime"].ToString();
                trainnumber.operation = da["operation"].ToString();
                trainnumber.Farenumber = Convert.ToInt32(da["Farenumber"]);
                list.Add(trainnumber);
            }
            return list;
        }
        /// <summary>
        /// 删除车次表信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int trainnumberDelete(int id)
        {
            string str = "delete from trainnumber where Trainnumberid=" + id;
            DbCommand cmd = db.GetSqlStringCommand(str);
            return db.ExecuteNonQuery(cmd);
        }
        /// <summary>
        /// 添加车次信息
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int trainnumberInsert(Travel_Model.trainnumber model)
        {
            string sql = "insert into trainnumber values(@Trainname,@Starttime,@Endtime,@Stop,@Stoptime,@operation,@Farenumber)";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@Trainname", DbType.String, model.Trainname);
            db.AddInParameter(cmd, "@Starttime", DbType.String, model.Starttime);
            db.AddInParameter(cmd, "@Endtime", DbType.String, model.Endtime);
            db.AddInParameter(cmd, "@Stop", DbType.String, model.Stop);
            db.AddInParameter(cmd, "@Stoptime", DbType.String, model.Stoptime);
            db.AddInParameter(cmd, "@operation", DbType.String, model.operation);
            db.AddInParameter(cmd, "@Farenumber", DbType.Int32, model.Farenumber);
            return db.ExecuteNonQuery(cmd);
        }
        /// <summary>
        /// 通过id找到要修改的行
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public trainnumber FindTrainnumberInforById(int id)
        {
            string str = "select * from trainnumber where Trainnumberid=" + id;
            DbCommand cmd = db.GetSqlStringCommand(str);
            trainnumber trainnumber = new trainnumber();
            IDataReader da = db.ExecuteReader(cmd);
            while (da.Read())
            {
                trainnumber.Trainnumberid = Convert.ToInt32(da["Trainnumberid"]);
                trainnumber.Trainname = da["Trainname"].ToString();
                trainnumber.Starttime = da["Starttime"].ToString();
                trainnumber.Endtime = da["Endtime"].ToString();
                trainnumber.Stop = da["Stop"].ToString();
                trainnumber.Stoptime = da["Stoptime"].ToString();
                trainnumber.operation = da["operation"].ToString();
                trainnumber.Farenumber = Convert.ToInt32(da["Farenumber"]);

            }
            return trainnumber;
        }
        /// <summary>
        /// 实现修改功能
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int trainnumberUpdate(Travel_Model.trainnumber model)
        {
            string sql = "update trainnumber set Trainname=@Trainname,Starttime=@Starttime,Endtime=@Endtime,Stop=@Stop,Stoptime=@Stoptime,operation=@operation,Farenumber=@Farenumber where Trainnumberid=@Trainnumberid";
            DbCommand cmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(cmd, "@Trainname", DbType.String, model.Trainname);
            db.AddInParameter(cmd, "@Starttime", DbType.String, model.Starttime);
            db.AddInParameter(cmd, "@Endtime", DbType.String, model.Endtime);
            db.AddInParameter(cmd, "@Stop", DbType.String, model.Stop);
            db.AddInParameter(cmd, "@Stoptime", DbType.String, model.Stoptime);
            db.AddInParameter(cmd, "@operation", DbType.String, model.operation);
            db.AddInParameter(cmd, "@Farenumber", DbType.Int32, model.Farenumber);
            db.AddInParameter(cmd, "@Trainnumberid", DbType.Int32, model.Trainnumberid);
            return db.ExecuteNonQuery(cmd);
        }
        /// <summary>
        /// 按照Trainname搜索车次信息
        /// </summary>
        /// <param name="Trainname"></param>
        /// <returns></returns>
        public List<trainnumber> SelectTrainnumberByTrainname(string Trainname)
        {
            string str = "select * from trainnumber where Trainname=@Trainname";
            DbCommand cmd = db.GetSqlStringCommand(str);
            List<trainnumber> list = new List<trainnumber>();
            IDataReader da = db.ExecuteReader(cmd);
            while (da.Read())
            {
                trainnumber trainnumber = new trainnumber();
                trainnumber.Trainnumberid = Convert.ToInt32(da["Trainnumberid"]);
                trainnumber.Trainname = da["Trainname"].ToString();
                trainnumber.Starttime = da["Starttime"].ToString();
                trainnumber.Endtime = da["Endtime"].ToString();
                trainnumber.Stop = da["Stop"].ToString();
                trainnumber.Stoptime = da["Stoptime"].ToString();
                trainnumber.operation = da["operation"].ToString();
                trainnumber.Farenumber = Convert.ToInt32(da["Farenumber"]);
                list.Add(trainnumber);
            }
            return list;
        }
        /// <summary>
        /// 显示座位类型
        /// </summary>
        /// <returns></returns>
        public List<seattype> showseattype()
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, "select * from seattype");
            List<seattype> list = new List<seattype>();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                seattype s = new seattype();
                s.id = Convert.ToInt32(item["seattypeid"]);
                s.seattypename = item["seattypename"].ToString();
                s.Price = item["Price"].ToString();
                s.Trainnumberid = Convert.ToInt32(item["Trainnumberid"]);
                list.Add(s);
            }
            return list;
        }
        /// <summary>
        /// 显示车次类型
        /// </summary>
        /// <returns></returns>
        public List<trainnumbertype> showtrainnumbertype()
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, "select * from trainnumbertype");
            List<trainnumbertype> list = new List<trainnumbertype>();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                trainnumbertype tp = new trainnumbertype();
                tp.id = Convert.ToInt32(item["trainnumbertypeid"]);
                tp.Trainnumberid = Convert.ToInt32(item["Trainnumberid"]);
                tp.Traintype = item["Traintype"].ToString();
                list.Add(tp);
            }
            return list;
        }
        /// <summary>
        /// 显示车次信息
        /// </summary>
        /// <returns></returns>
        public List<trainnumber> showtrainnumber()
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, "select distinct  t.Trainname,t.Starttime,t.[Stop],t.Endtime,t.startstation,t.Endstation,t.operation,t.Farenumber,seattype.seattypename,seattype.Price from (select trainnumber.Trainname,[Stop],trainnumber.Starttime,trainnumber.Endtime, (select station.startstation from station) startstation, (select station.Endstation from station) Endstation, trainnumber.operation,trainnumber.Farenumber from trainnumber,station,seattype) t,seattype");
            List<trainnumber> list = new List<trainnumber>();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                trainnumber t = new trainnumber();
                t.Trainname = item["Trainname"].ToString();
                t.Starttime =Convert.ToDateTime( item["Starttime"]).ToString("yyyy-MM-dd hh:mm:ss");
                t.Endtime = Convert.ToDateTime(item["Endtime"]).ToString("yyyy-MM-dd hh:mm:ss");
                t.Stop = item["Stop"].ToString();
                t.operation = item["operation"].ToString();
                t.Farenumber = Convert.ToInt32(item["Farenumber"]);
                t.seattypename = item["seattypename"].ToString();
                t.Price = item["Price"].ToString();
                t.startstation = item["startstation"].ToString();
                t.Endstation = item["Endstation"].ToString();
                list.Add(t);
            }
            return list;
        }
        /// <summary>
        /// 显示时段信息
        /// </summary>
        /// <returns></returns>
        public List<timeframe> showtimeframe()
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, "select * from timeframe");
            List<timeframe> list = new List<timeframe>();
            foreach (DataRow item in ds.Tables[0].Rows)
            {
                timeframe t = new timeframe();
                t.id = Convert.ToInt32(item["timeframeid"]);
                t.Timecontent = item["Timecontent"].ToString();
                list.Add(t);
            }
            return list;
        }
        /// <summary>
        /// 显示城市信息
        /// </summary>
        /// <returns></returns>
        public List<city> showcity()
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, "select * from city");
            List<city> list = new List<city>();
            foreach (DataRow item in ds.Tables)
            {
                city c = new city();
                c.cityid = Convert.ToInt32(item["cityid"]);
                c.Cityname = item["Cityname"].ToString();
                list.Add(c);
            }
            return list;
        }
        /// <summary>
        /// 显示车站信息
        /// </summary>
        /// <returns></returns>
        public List<station> showstation()
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, "select * from station");
            List<station> list = new List<station>();
            foreach (DataRow item in ds.Tables)
            {
                station s = new station();
                s.id = Convert.ToInt32(item["stationid"]);
                s.startstation = item["startstation"].ToString();
                s.Endstation = item["Endstation"].ToString();
                list.Add(s);
            }
            return list;
        }
        /// <summary>
        /// 绑定证件信息下拉框
        /// </summary>
        /// <returns></returns>
        public List<papersType> showpapersType()
        {
            DataSet ds = db.ExecuteDataSet(CommandType.Text, "select * from papersType");
            List<papersType> list = new List<papersType>();
            foreach (DataRow item in ds.Tables)
            {
                papersType p = new papersType();
                p.pid = Convert.ToInt32(item["pid"]);
                p.PaperName = item["PaperName"].ToString();
                list.Add(p);
            }
            return list;
        }

    }
}
